package com.japp.manager.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.japp.base.BaseService;
import com.japp.manager.bean.EngageInBean;
import com.japp.manager.bean.RealityEI1Bean;
import com.japp.manager.bean.RealityEIBean;
import com.japp.manager.bean.TopBean;
import com.japp.manager.mapper.RealityEIMapper;
import com.japp.tools.common.Common;
import com.japp.tools.common.JappLog;

/**
 * 经营报表
 * 
 * @author Administrator
 * 
 */
@Service
public class RealityEIService extends SqlSessionDaoSupport implements
		BaseService {

	@Autowired
	private RealityEIMapper realityEIMapper;
	@Autowired
	private SqlSessionTemplate template;
	@Autowired
	private EngageInService engageInService;

	@Transactional(readOnly=true)
	public Map<String, Object> findRealityEI(RealityEIBean r,RealityEI1Bean r1) {
		if("2".equals(r.getEi_date_type())){
			r.setEi_cdate(String.valueOf(Common.getThisDateWEEK(r.getEi_date())));
		}
		String date = r.getEi_date();
		String cdate = r.getEi_cdate();
		String date_type = r.getEi_date_type();
		String power = r.getEi_power();
		String dpid = r.getEi_dpid();
		String ucode = r.getEi_ucode();
		if("1".equals(power)){
			dpid = "";
			ucode = "";
		}else if ("2".equals(power)) {
			ucode = "";
		}
		power = "";
		r1.setEi_date(date);
		r1.setEi_cdate(cdate);
		r1.setEi_date_type(date_type);
		r1.setEi_dpid(dpid);
		r1.setEi_power(power);
		r1.setEi_ucode(ucode);
		Map<String, Object> map = new HashMap<String, Object>();
		Connection conn = null;
		CallableStatement call = null;
		CallableStatement call1 = null;
		ResultSet rs = null;
		try {
			conn = template.getSqlSessionFactory().openSession().getConnection();
			/******正常数据开始******/
			JappLog.log.info("正常数据: 经营数据开始");
			//经营数据
			call = conn.prepareCall("{CALL PRO_REALITY_EI(?,?,?,?,?,?)}");
			call.setString(1, date_type);
			call.setString(2, date);
			call.setString(3, cdate);
			call.setString(4, dpid);
			call.setString(5, ucode);
			call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			call.execute();
			rs = (ResultSet) call.getObject(6);
			if (rs.next()) {
				r = (RealityEIBean) Common.getObjView(r, rs);
			}
			//费用
			call1 = conn.prepareCall("{CALL PRO_REALITY_EI1(?,?,?,?,?,?)}");
			call1.setString(1, date_type);
			call1.setString(2, date);
			call1.setString(3, cdate);
			call1.setString(4, dpid);
			call1.setString(5, ucode);
			call1.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			call1.execute();
			rs = (ResultSet) call1.getObject(6);
			if (rs.next()) {
				r1 = (RealityEI1Bean) Common.getObjView(r1, rs);
			}
			r.setB13(r1.getN16());
			r.setB15(r1.getN19());
			r.setB16(r.getB16().subtract(r1.getN19()));
			map.put("r", r);
			map.put("r1", r1);
			/******正常数据结束******/
			/******年度统计开始******/
			JappLog.log.info("年度统计: 经营数据开始");
			r = new RealityEIBean();
			r.setEi_date(date);
			r.setEi_cdate(cdate);
			r.setEi_date_type(date_type);
			r.setEi_dpid(dpid);
			r.setEi_power(power);
			r.setEi_ucode(ucode);
			r1 = new RealityEI1Bean();
			r1.setEi_date(date);
			r1.setEi_cdate(cdate);
			r1.setEi_date_type(date_type);
			r1.setEi_dpid(dpid);
			r1.setEi_power(power);
			r1.setEi_ucode(ucode);
			//经营数据
			call.close();
			call = null;
			call = conn.prepareCall("{CALL PRO_REALITY_EI(?,?,?,?,?,?)}");
			call.setString(1, date_type);
			call.setString(2, date);
			call.setString(3, "ALL");
			call.setString(4, dpid);
			call.setString(5, ucode);
			call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			call.execute();
			rs = (ResultSet) call.getObject(6);
			if (rs.next()) {
				r = (RealityEIBean) Common.getObjView(r, rs);
			}
			//费用
			call1.close();
			call1 = null;
			call1 = conn.prepareCall("{CALL PRO_REALITY_EI1(?,?,?,?,?,?)}");
			call1.setString(1, date_type);
			call1.setString(2, date);
			call1.setString(3, "ALL");
			call1.setString(4, dpid);
			call1.setString(5, ucode);
			call1.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			call1.execute();
			rs = (ResultSet) call1.getObject(6);
			if (rs.next()) {
				r1 = (RealityEI1Bean) Common.getObjView(r1, rs);
			}
			r.setB13(r1.getN16());
			r.setB15(r1.getN19());
			r.setB16(r.getB16().subtract(r1.getN19()));
			map.put("c", r);
			map.put("e1", r1);
			/******年度统计结束******/
			/******计划开始******/
			JappLog.log.info("计划数据: 经营数据开始");
			EngageInBean en = new EngageInBean();
			en.setEn_type(date_type);
			en.setEn_submit_date(date);
			en.setEn_submit_cdate(cdate);
			en.setEn_submit_ucode(ucode);
			en.setEn_submit_dpid(dpid);
			en.setEn_power(power);
			for (int i = Integer.valueOf(date_type); i < 7; i++) {
				en.setEn_type(String.valueOf(i));
				if(i==4){
					en.setEn_submit_cdate(Common.getThisDateJdStart(cdate,date));
				}else if(i==5){
					en.setEn_submit_cdate(Common.getThisDateYear1(date));
				}
				map.put("en_"+i, engageInService.findEngageInByBean(en));
			}
			/******计划结束******/
			/*****上期数查询开始*****/
			JappLog.log.info("正常上期数据: 经营数据开始");
			r = new RealityEIBean();
			r.setEi_date(date);
			r.setEi_cdate(cdate);
			r.setEi_date_type(date_type);
			r.setEi_dpid(dpid);
			r.setEi_power(power);
			r.setEi_ucode(ucode);
			r1 = new RealityEI1Bean();
			r1.setEi_date(date);
			r1.setEi_cdate(cdate);
			r1.setEi_date_type(date_type);
			r1.setEi_dpid(dpid);
			r1.setEi_power(power);
			r1.setEi_ucode(ucode);
			//判断开始
			//判断开始
			if("1".equals(date_type)){
				date = Common.getThisDateYMD(date, "yyyy-MM-dd", "D", -1);
			}else if("2".equals(date_type)){
				date = Common.getThisDateYMD(date, "yyyy-MM-dd", "IW", -1);
				cdate = String.valueOf(Common.getThisDateWEEK(date));
			}else if("3".equals(date_type)){
				date = Common.getThisDateYMD(date, "yyyy-MM", "M", -1);
			}else if("4".equals(date_type)){
				if("1".equals(cdate)){
					cdate = "4";
					date = (date.length()==4?(date+"-01-01"):date);
					date = Common.getThisDateYMD(date, "yyyy-MM-dd", "Y", -1);
				}else{
					date = date.substring(0,4)+Common.getThisDateJdStart(cdate,date).substring(4,10);
					cdate = String.valueOf(Integer.valueOf(cdate)-1);
				}
			}else if("5".equals(date_type)){
				if("1".equals(cdate)){
					cdate = "2";
					date = Common.getThisDateYMD(date.substring(0,4), "yyyy", "Y", -1);
				}else{
					cdate = "1";
				}
			}else if("6".equals(date_type)){
				date = Common.getThisDateYMD(date.substring(0,4), "yyyy", "Y", -1);
			}
			//经营数据
			call.close();
			call = null;
			call = conn.prepareCall("{CALL PRO_REALITY_EI(?,?,?,?,?,?)}");
			call.setString(1, date_type);
			call.setString(2, date);
			call.setString(3, cdate);
			call.setString(4, dpid);
			call.setString(5, ucode);
			call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			call.execute();
			rs = (ResultSet) call.getObject(6);
			if (rs.next()) {
				r = (RealityEIBean) Common.getObjView(r, rs);
			}
			//费用
			call1.close();
			call1 = null;
			call1 = conn.prepareCall("{CALL PRO_REALITY_EI1(?,?,?,?,?,?)}");
			call1.setString(1, date_type);
			call1.setString(2, date);
			call1.setString(3, cdate);
			call1.setString(4, dpid);
			call1.setString(5, ucode);
			call1.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			call1.execute();
			rs = (ResultSet) call1.getObject(6);
			if (rs.next()) {
				r1 = (RealityEI1Bean) Common.getObjView(r1, rs);
			}
			r.setB13(r1.getN16());
			r.setB15(r1.getN19());
			r.setB16(r.getB16().subtract(r1.getN19()));
			map.put("r-1", r);
			map.put("r1-1", r1);
			/*****上期数查询结束*****/
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map;
	}
	/**
	 * 经营排行查询
	 * @param t
	 * @return
	 */
	public Map<String, Object> findRealityTop(TopBean t){
		Map<String, Object> map = new HashMap<String, Object>();
		if("2".equals(t.getDate_type())){
			t.setCdate(String.valueOf(Common.getThisDateWEEK(t.getDate())));
		}
		//t.setDate("2013-06-12");
		//t.setCdate("24");
		//t.setDate_type("2");
		String date_type = t.getDate_type();
		String date = t.getDate();
		String cdate = t.getCdate();
		//String top_type = "1";
		OracleConnection conn = null;
		Connection con = null;
		OracleCallableStatement call = null;
		try {
			con = template.getSqlSessionFactory().openSession().getConnection();
			C3P0NativeJdbcExtractor c3p0 = new C3P0NativeJdbcExtractor();
			conn = (OracleConnection)c3p0.getNativeConnection(con);
			call = (OracleCallableStatement)conn.prepareCall("{CALL PRO_REALITY_EITOP(?,?,?,?)}");
			call.setString(1, date_type);
			call.setString(2, date);
			call.setString(3, cdate);
			call.registerOutParameter(4, OracleTypes.ARRAY,"EMP_ARRAY_OBJ");
			call.execute();
			ARRAY array = call.getARRAY(4);			//获取返回参数
			lt = getTopList(array,0);
			map.put("khqy", lt);
			lt = getTopList(array,1);
			map.put("jye", lt);
			lt = getTopList(array,2);
			map.put("sxfsr", lt);
			lt = getTopList(array,3);
			map.put("glfy", lt);
			lt = getTopList(array,4);
			map.put("yyfy", lt);
			lt = getTopList(array,5);
			map.put("yyly", lt);
			lt = getTopList(array,6);
			map.put("ywzdf", lt);
			lt = getTopList(array,7);
			map.put("clf", lt);
			lt = getTopList(array,8);
			if(lt!=null&&lt.size()>0){
				for (TopBean ts: lt) {
					map.put(ts.getData_type(), ts.getType_sum());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				con.close();
				JappLog.log.info("xxx Connection Closed");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map;
	}
	private TopBean top = new TopBean();
	private List<TopBean> lt = null;
	public List<TopBean> getTopList(ARRAY array,int index)throws Exception{
		lt = new ArrayList<>();
		array = (ARRAY)array.getOracleArray()[index];
		Datum[] dtm = array.getOracleArray();
		int len = dtm.length;
		if(len==0){
			return null;
		}
		Object[] obj = null;
		for (int i = 0; i < dtm.length; i++) {
		    STRUCT st = (STRUCT) dtm[i];		//需要否则乱码nls_charset12.jar  oracle11g orai18n.jar
		    if(st!=null){
		    	obj = st.getAttributes();
			    if(obj!=null&&obj.length!=0){
			    	if(obj[0]==null){
			    		continue;
			    	}
			    	top = new TopBean();
			    	top.setQ(obj[0].toString());
				    top.setData_type(obj[1].toString());
				    top.setDp_id(obj[2].toString());
				    top.setDp_name(obj[3].toString());
				    top.setUcode(obj[4].toString());
				    top.setUname(obj[5].toString());
				    top.setType_csum(Double.valueOf(obj[6].toString()));
				    top.setType_sum(Double.valueOf(obj[7].toString()));
				    lt.add(top);
			    }
		    }
		}
		return lt;
	}

}
